{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>speed</th>\n",
       "      <th>travel_type</th>\n",
       "      <th>current_position</th>\n",
       "      <th>equip_id</th>\n",
       "      <th>duration_time</th>\n",
       "      <th>truck_id</th>\n",
       "      <th>...</th>\n",
       "      <th>装载机v2v刹停</th>\n",
       "      <th>障碍物停障</th>\n",
       "      <th>故障诊断下发刹停</th>\n",
       "      <th>人工接管触发刹停</th>\n",
       "      <th>遥控器触发刹停</th>\n",
       "      <th>控制模块AEB</th>\n",
       "      <th>矿卡急停按钮触发刹停</th>\n",
       "      <th>区域管控停车</th>\n",
       "      <th>路口管控停车</th>\n",
       "      <th>安全模式刹停</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-09-13 08:13:44</td>\n",
       "      <td>2024-09-13 08:13:44</td>\n",
       "      <td>44.064899</td>\n",
       "      <td>81.427478</td>\n",
       "      <td>0.0</td>\n",
       "      <td>9216</td>\n",
       "      <td>20.0</td>\n",
       "      <td>154</td>\n",
       "      <td>0.0</td>\n",
       "      <td>Z02</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>人工接管触发刹停</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>矿卡急停按钮触发刹停</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-09-13 08:13:45</td>\n",
       "      <td>2024-09-13 08:19:40</td>\n",
       "      <td>44.064900</td>\n",
       "      <td>81.427478</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8192</td>\n",
       "      <td>20.0</td>\n",
       "      <td>154</td>\n",
       "      <td>355.0</td>\n",
       "      <td>Z02</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>矿卡急停按钮触发刹停</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-09-13 08:19:41</td>\n",
       "      <td>2024-09-13 08:25:48</td>\n",
       "      <td>44.064899</td>\n",
       "      <td>81.427479</td>\n",
       "      <td>0.0</td>\n",
       "      <td>65536</td>\n",
       "      <td>20.0</td>\n",
       "      <td>154</td>\n",
       "      <td>367.0</td>\n",
       "      <td>Z02</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>安全模式刹停</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2024-09-13 08:33:14</td>\n",
       "      <td>2024-09-13 08:47:16</td>\n",
       "      <td>44.054522</td>\n",
       "      <td>81.442039</td>\n",
       "      <td>0.0</td>\n",
       "      <td>8</td>\n",
       "      <td>1.0</td>\n",
       "      <td>154</td>\n",
       "      <td>842.0</td>\n",
       "      <td>Z02</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2024-09-13 08:47:17</td>\n",
       "      <td>2024-09-13 08:47:24</td>\n",
       "      <td>44.054522</td>\n",
       "      <td>81.442039</td>\n",
       "      <td>0.0</td>\n",
       "      <td>264</td>\n",
       "      <td>1.0</td>\n",
       "      <td>154</td>\n",
       "      <td>7.0</td>\n",
       "      <td>Z02</td>\n",
       "      <td>...</td>\n",
       "      <td></td>\n",
       "      <td>障碍物停障</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 31 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            start_time             end_time   latitude  longitude  speed  \\\n",
       "0  2024-09-13 08:13:44  2024-09-13 08:13:44  44.064899  81.427478    0.0   \n",
       "1  2024-09-13 08:13:45  2024-09-13 08:19:40  44.064900  81.427478    0.0   \n",
       "2  2024-09-13 08:19:41  2024-09-13 08:25:48  44.064899  81.427479    0.0   \n",
       "3  2024-09-13 08:33:14  2024-09-13 08:47:16  44.054522  81.442039    0.0   \n",
       "4  2024-09-13 08:47:17  2024-09-13 08:47:24  44.054522  81.442039    0.0   \n",
       "\n",
       "   travel_type  current_position  equip_id  duration_time truck_id  ...  \\\n",
       "0         9216              20.0       154            0.0      Z02  ...   \n",
       "1         8192              20.0       154          355.0      Z02  ...   \n",
       "2        65536              20.0       154          367.0      Z02  ...   \n",
       "3            8               1.0       154          842.0      Z02  ...   \n",
       "4          264               1.0       154            7.0      Z02  ...   \n",
       "\n",
       "  装载机v2v刹停  障碍物停障 故障诊断下发刹停  人工接管触发刹停 遥控器触发刹停 控制模块AEB  矿卡急停按钮触发刹停 区域管控停车  \\\n",
       "0                           人工接管触发刹停                  矿卡急停按钮触发刹停          \n",
       "1                                                     矿卡急停按钮触发刹停          \n",
       "2                                                                         \n",
       "3                                                                         \n",
       "4           障碍物停障                                                         \n",
       "\n",
       "  路口管控停车  安全模式刹停  \n",
       "0                 \n",
       "1                 \n",
       "2         安全模式刹停  \n",
       "3                 \n",
       "4                 \n",
       "\n",
       "[5 rows x 31 columns]"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('input\\速度为0的记录.csv')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 关联停车与故障"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from pandas import Interval\n",
    "\n",
    "# 示例数据\n",
    "data_A = {\n",
    "    'start_time': [\n",
    "        '2023-10-01 00:00:01', '2023-10-01 00:00:10', '2023-10-01 00:00:20',\n",
    "        '2023-10-01 00:00:30', '2023-10-01 00:00:40'\n",
    "    ],\n",
    "    'end_time': [\n",
    "        '2023-10-01 00:00:05', '2023-10-01 00:00:15', '2023-10-01 00:00:25',\n",
    "        '2023-10-01 00:00:35', '2023-10-01 00:00:45'\n",
    "    ],\n",
    "    'parking_reason': [\n",
    "        'maintenance', 'fault', 'maintenance', 'fault', 'maintenance'\n",
    "    ]\n",
    "}\n",
    "\n",
    "data_B = {\n",
    "    'start_time': [\n",
    "        '2023-10-01 00:00:02', '2023-10-01 00:00:11', '2023-10-01 00:00:31',\n",
    "        '2023-10-01 00:00:12', '2023-10-01 00:00:32'\n",
    "    ],\n",
    "    'end_time': [\n",
    "        '2023-10-01 00:00:06', '2023-10-01 00:00:16', '2023-10-01 00:00:36',\n",
    "        '2023-10-01 00:00:14', '2023-10-01 00:00:34'\n",
    "    ],\n",
    "    'fault_reason': [\n",
    "        'engine failure', 'battery issue', 'sensor malfunction',\n",
    "        'software issue', 'hardware issue'\n",
    "    ]\n",
    "}\n",
    "\n",
    "# 创建数据框\n",
    "df_A = pd.DataFrame(data_A)\n",
    "df_B = pd.DataFrame(data_B)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>parking_reason</th>\n",
       "      <th>new</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2023-10-01 00:00:01</td>\n",
       "      <td>2023-10-01 00:00:05</td>\n",
       "      <td>maintenance</td>\n",
       "      <td>2023-10-01 00:00:01,2023-10-01 00:00:05,mainte...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2023-10-01 00:00:10</td>\n",
       "      <td>2023-10-01 00:00:15</td>\n",
       "      <td>fault</td>\n",
       "      <td>2023-10-01 00:00:10,2023-10-01 00:00:15,fault,nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2023-10-01 00:00:20</td>\n",
       "      <td>2023-10-01 00:00:25</td>\n",
       "      <td>maintenance</td>\n",
       "      <td>2023-10-01 00:00:20,2023-10-01 00:00:25,mainte...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2023-10-01 00:00:30</td>\n",
       "      <td>2023-10-01 00:00:35</td>\n",
       "      <td>fault</td>\n",
       "      <td>2023-10-01 00:00:30,2023-10-01 00:00:35,fault,nan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2023-10-01 00:00:40</td>\n",
       "      <td>2023-10-01 00:00:45</td>\n",
       "      <td>maintenance</td>\n",
       "      <td>2023-10-01 00:00:40,2023-10-01 00:00:45,mainte...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            start_time             end_time parking_reason  \\\n",
       "0  2023-10-01 00:00:01  2023-10-01 00:00:05    maintenance   \n",
       "1  2023-10-01 00:00:10  2023-10-01 00:00:15          fault   \n",
       "2  2023-10-01 00:00:20  2023-10-01 00:00:25    maintenance   \n",
       "3  2023-10-01 00:00:30  2023-10-01 00:00:35          fault   \n",
       "4  2023-10-01 00:00:40  2023-10-01 00:00:45    maintenance   \n",
       "\n",
       "                                                 new  \n",
       "0  2023-10-01 00:00:01,2023-10-01 00:00:05,mainte...  \n",
       "1  2023-10-01 00:00:10,2023-10-01 00:00:15,fault,nan  \n",
       "2  2023-10-01 00:00:20,2023-10-01 00:00:25,mainte...  \n",
       "3  2023-10-01 00:00:30,2023-10-01 00:00:35,fault,nan  \n",
       "4  2023-10-01 00:00:40,2023-10-01 00:00:45,mainte...  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_A['new'] = df_A.apply(lambda row: ','.join(row.astype(str)), axis=1)\n",
    "df_A"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[Timestamp('2023-10-01 00:00:01'),\n",
       " Timestamp('2023-10-01 00:00:05'),\n",
       " 'maintenance']"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_A.loc[0].to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "    速度  状态\n",
      "0    1  正常\n",
      "1    0   0\n",
      "2    0   3\n",
      "3    0   3\n",
      "4    0   3\n",
      "5    0   3\n",
      "6    0   3\n",
      "7    0   3\n",
      "8    3  正常\n",
      "9    5  正常\n",
      "10   5  正常\n",
      "11   5  正常\n",
      "12   0  12\n",
      "13   0  12\n",
      "14   0  12\n",
      "15   0  12\n",
      "16   0  12\n",
      "17   0  12\n",
      "18   6  正常\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 示例数据\n",
    "data = {\n",
    "    '速度': [1, 0, 0, 0, 0, 0, 0, 0, 3, 5, 5, 5, 0, 0, 0, 0, 0, 0, 6],\n",
    "    '状态': ['正常', '0', '3', '3', '3', '3', '正常', '正常', '正常', '正常', '正常', '正常', '12', '12', '12', '12', '正常', '正常', '正常']\n",
    "}\n",
    "\n",
    "# 创建DataFrame\n",
    "df = pd.DataFrame(data)\n",
    "df\n",
    "\n",
    "# 找到所有速度为0且状态为正常的行\n",
    "mask = (df['速度'] == 0) & (df['状态'] == '正常')\n",
    "\n",
    "# 初始化一个变量来存储最近的停车代码\n",
    "last_parking_code = None\n",
    "\n",
    "# 遍历DataFrame，更新状态\n",
    "for i in range(len(df)):\n",
    "    if mask[i]:\n",
    "        df.at[i, '状态'] = last_parking_code\n",
    "    elif df.at[i, '状态'] != '正常':\n",
    "        last_parking_code = df.at[i, '状态']\n",
    "\n",
    "# 输出更新后的DataFrame\n",
    "print(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "           start_time            end_time\n",
      "0 2024-10-13 08:00:00 2024-10-13 08:01:59\n",
      "1 2024-10-13 08:02:00 2024-10-13 08:03:59\n",
      "2 2024-10-13 08:05:00 2024-10-13 08:06:59\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 假设df是你的DataFrame\n",
    "data = {\n",
    "    'start_time': ['2024-10-13 08:00:00', '2024-10-13 08:02:00', '2024-10-13 08:05:00'],\n",
    "    'end_time': ['2024-10-13 08:01:59', '2024-10-13 08:03:59', '2024-10-13 08:06:59'],\n",
    "    'status': ['active', 'active', 'inactive']\n",
    "}\n",
    "df = pd.DataFrame(data)\n",
    "\n",
    "# 将时间列转换为datetime类型\n",
    "df['start_time'] = pd.to_datetime(df['start_time'])\n",
    "df['end_time'] = pd.to_datetime(df['end_time'])\n",
    "\n",
    "# 创建一个布尔列，标记是否需要合并\n",
    "df['to_merge'] = (df['status'].shift() == df['status']) & ((df['start_time'] - df['end_time'].shift()) <= pd.Timedelta('120 seconds'))\n",
    "\n",
    "# 创建一个新的列，用于标识每组应该合并的行\n",
    "df['group_id'] = (df['to_merge'] != df['to_merge'].shift()).cumsum()\n",
    "\n",
    "# 按照status和group_id分组，并计算每个组的最小开始时间和最大结束时间\n",
    "merged_df = df.groupby(['status', 'group_id']).agg({\n",
    "    'start_time': 'min',\n",
    "    'end_time': 'max'\n",
    "}).reset_index(drop=True)\n",
    "\n",
    "print(merged_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import sqlalchemy\n",
    "from pathlib import Path\n",
    "import gc\n",
    "import matplotlib.pyplot as plt\n",
    "from matplotlib.patches import Rectangle\n",
    "import matplotlib.dates as mdates\n",
    "import warnings\n",
    "\n",
    "warnings.filterwarnings('ignore')\n",
    "# plt.rcParams['figure.figsize'] = [6, 4]\n",
    "plt.rcParams['figure.autolayout'] = True\n",
    "plt.rcParams['font.family'] = 'SimHei'\n",
    "plt.rcParams['axes.unicode_minus'] = False\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "def read_sql(sql, engine):\n",
    "    df_tmp = pd.read_sql(sql, engine)\n",
    "    return df_tmp\n",
    "\n",
    "def read_trucks_from_excel(path):\n",
    "    truck_info = pd.read_excel(path)\n",
    "    truck_info.columns = ['equip_id', 'truck_id']\n",
    "    truck_info['truck_id'] = truck_info['truck_id'].astype('str')\n",
    "    truck_info['truck_id'] = truck_info['truck_id'].str.strip()\n",
    "    \n",
    "    return truck_info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {},
   "outputs": [],
   "source": [
    "truck_info = read_trucks_from_excel('input\\庆华项目配置.xlsx')\n",
    "loc_df = pd.read_csv('input\\stb_trajectory_log-20240919.csv', parse_dates=['ts'])\n",
    "loc_df.rename(columns={ 'equipment_id':'equip_id'}, inplace=True)\n",
    "loc_df['ts'] = loc_df['ts'].dt.strftime('%Y-%m-%d %H:%M:%S')\n",
    "loc_df['ts'] = pd.to_datetime(loc_df['ts'])\n",
    "loc_df = pd.merge(loc_df, truck_info, how='inner', on='equip_id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 替换 travel_type 为 NaN\n",
    "tmp_df = loc_df[['ts', 'speed', 'travel_type', 'truck_id']].copy()\n",
    "tmp_df['travel_type'] = tmp_df['travel_type'].replace(0, np.nan)\n",
    "\n",
    "# 对每辆车进行分组操作\n",
    "grouped = tmp_df.groupby('truck_id')\n",
    "\n",
    "# 对每组进行前向填充和后向填充\n",
    "tmp_df['forward_fill'] = grouped['travel_type'].fillna(method='ffill')\n",
    "tmp_df['backward_fill'] = grouped['travel_type'].fillna(method='bfill')\n",
    "\n",
    "# 找出 speed == 0 且 travel_type 为空的行\n",
    "mask = (tmp_df['speed'] == 0) & (tmp_df['travel_type'].isna())\n",
    "\n",
    "# 计算每行的时间差并填充最近的 travel_type\n",
    "tmp_df['time_diff_forward'] = abs((tmp_df['ts'] - grouped['ts'].shift(1)).dt.total_seconds())\n",
    "tmp_df['time_diff_backward'] = abs((tmp_df['ts'] - grouped['ts'].shift(-1)).dt.total_seconds())\n",
    "\n",
    "# 在时间差限制内（120秒），选择最近的 travel_type 进行填充\n",
    "tmp_df['travel_type'] = np.where(\n",
    "    (tmp_df['time_diff_forward'] <= 120) & mask,\n",
    "    tmp_df['forward_fill'],\n",
    "    np.where(\n",
    "        (tmp_df['time_diff_backward'] <= 120) & mask,\n",
    "        tmp_df['backward_fill'],\n",
    "        tmp_df['travel_type']\n",
    "    )\n",
    ")\n",
    "\n",
    "# 清理不再需要的临时列\n",
    "# tmp_df.drop(['forward_fill', 'backward_fill', 'time_diff_forward', 'time_diff_backward'], axis=1, inplace=True)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {},
   "outputs": [],
   "source": [
    "tmp_df['travel_type'] = tmp_df['travel_type'].fillna('0')\n",
    "loc_df.drop(columns=['speed', 'travel_type', 'truck_id', 'ts'], inplace=True)\n",
    "loc_df = pd.concat([loc_df, tmp_df], axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "loc_df = loc_df.sort_values(by=['ts']).reset_index(drop=True)\n",
    "grouped = loc_df.groupby('equip_id')\n",
    "\n",
    "result = []\n",
    "for truck, group in grouped:\n",
    "    group['status_change'] = ((group['travel_type'] != group['travel_type'].shift()) |\n",
    "                            ((group['ts'] - group['ts'].shift()).dt.total_seconds() > 120))\n",
    "    group['status_change'] = group['status_change'].cumsum()\n",
    " \n",
    "    for _, sub_group in group.groupby('status_change'):\n",
    "        start_time = sub_group['ts'].min()\n",
    "        end_time = sub_group['ts'].max()\n",
    "        sub_group = sub_group.reset_index(drop=True)\n",
    "        travel_type = sub_group.at[0, 'travel_type']\n",
    "        \n",
    "        result.append([start_time, end_time, travel_type, truck])\n",
    "\n",
    "result_df = pd.DataFrame(result, columns=['start_time', 'end_time', 'travel_type', 'equip_id'])\n",
    "result_df['duration_time'] = (result_df['end_time'] - result_df['start_time']).dt.total_seconds()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "      <th>status_type</th>\n",
       "      <th>equip_id</th>\n",
       "      <th>duration_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-09-13 08:13:35</td>\n",
       "      <td>2024-09-13 08:13:41</td>\n",
       "      <td>8192</td>\n",
       "      <td>154</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-09-13 08:13:42</td>\n",
       "      <td>2024-09-13 08:13:44</td>\n",
       "      <td>9216</td>\n",
       "      <td>154</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-09-13 08:13:45</td>\n",
       "      <td>2024-09-13 08:19:35</td>\n",
       "      <td>8192</td>\n",
       "      <td>154</td>\n",
       "      <td>350.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           start_time            end_time  status_type  equip_id  \\\n",
       "0 2024-09-13 08:13:35 2024-09-13 08:13:41         8192       154   \n",
       "1 2024-09-13 08:13:42 2024-09-13 08:13:44         9216       154   \n",
       "2 2024-09-13 08:13:45 2024-09-13 08:19:35         8192       154   \n",
       "\n",
       "   duration_time  \n",
       "0            6.0  \n",
       "1            2.0  \n",
       "2          350.0  "
      ]
     },
     "execution_count": 104,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "result_df['travel_type'] = result_df['travel_type'].astype(int)\n",
    "result_df.rename(columns={'travel_type': 'status_type'}, inplace=True)\n",
    "result_df.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [],
   "source": [
    "def compress_again(df, status_name=None):\n",
    "    if status_name:\n",
    "        df.rename(columns={status_name:'status'}, inplace=True)\n",
    "    \n",
    "    groups = df.groupby(['truck_id'])\n",
    "    result = []\n",
    "\n",
    "    for _, group in groups:\n",
    "        group = group.sort_values(by='start_time').reset_index(drop=True)\n",
    "        group['status_change'] = (group['status'] != group['status'].shift()) | (\n",
    "            (group['start_time'] - group['end_time'].shift()).dt.total_seconds() > 1 )\n",
    "        \n",
    "        group['sub_group_id'] = group['status_change'].cumsum()\n",
    "        sub_grouped = group.groupby(['sub_group_id'])\n",
    "        \n",
    "        for _, sub_group in sub_grouped:\n",
    "            sub_group = sub_group.reset_index(drop=True)\n",
    "            start_time = sub_group['start_time'].min()\n",
    "            end_time = sub_group['end_time'].max()\n",
    "            row = sub_group.loc[[0]].copy()\n",
    "            row.drop(columns=['status_change', 'sub_group_id'], inplace=True)\n",
    "            row.at[0, 'start_time'] = start_time\n",
    "            row.at[0, 'end_time'] = end_time\n",
    "            row = row.to_numpy()[0].tolist()\n",
    "            result.append(row)\n",
    "            \n",
    "    result_df = pd.DataFrame(result, columns=df.columns)\n",
    "    result_df['duration_time'] = (result_df['end_time'] - result_df['start_time']).dt.total_seconds()\n",
    "\n",
    "    return result_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Timestamp('2020-09-09 19:00:05')"
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "time = pd.to_datetime('2020-09-09 19:00:00')\n",
    "delta = pd.Timedelta(5, unit='s'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Distance between the two points in meters: 3.089400617427073\n"
     ]
    }
   ],
   "source": [
    "from math import radians, sin, cos, sqrt, asin\n",
    "\n",
    "def haversine(lat1, lon1, lat2, lon2):\n",
    "    R = 6371e3  # 地球平均半径，单位米\n",
    "    \n",
    "    dLat = radians(lat2 - lat1)\n",
    "    dLon = radians(lon2 - lon1)\n",
    "    \n",
    "    lat1, lon1, lat2 = map(radians, (lat1, lon1, lat2))\n",
    "    \n",
    "    a = sin(dLat/2)**2 + cos(lat1) * cos(lat2) * sin(dLon/2)**2\n",
    "    c = 2 * asin(sqrt(a))\n",
    "    \n",
    "    distance = R * c\n",
    "    return distance\n",
    "\n",
    "lat1, lon1 = 44.0495373, 81.4390937\n",
    "lat2, lon2 = 44.049562, 81.439076\n",
    "\n",
    "distance = haversine(lat1, lon1, lat2, lon2)\n",
    "print(\"Distance between the two points in meters:\", distance)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "myenv",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
